<?php
class keu_posting_model extends MY_Model {
	var $model = 'keu_posting';
	
	
	function GetJSON()
	{
		$ls_sql	= "	SELECT	TRANS.ID_DOK , OFC.NAMA_KANTOR, TO_CHAR(TRANS.TGL_TRANSAKSI,'DD/MM/YYYY') TGL_TRANSAKSI, 
							TRANS.KETERANGAN, TRANS.ID_COA,  TRANS.KD_VALUTA
					FROM	KEU_TRANSAKSI TRANS, SW_KANTOR OFC
					WHERE	
							OFC.KD_KANTOR = TRANS.KD_KANTOR  AND
							NVL(STATUS_VERIFIKASI,'N')='Y' AND
							NVL(STATUS_POSTING,'N')='N' AND
							TRANS.TGL_TRANSAKSI BETWEEN 
							TO_DATE('".date_format3(post('date_from'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND 
							TO_DATE('".date_format3(post('date_to'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND					
							(
								UPPER(OFC.NAMA_KANTOR) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(TRANS.KD_VALUTA) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(TRANS.KETERANGAN) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(TRANS.ID_DOK) LIKE UPPER('%".post('keyword')."%')
							)
					ORDER BY ".(post('sort')=='' ? 'ID_DOK' : post('sort'))." ".(post('order')=='' ? 'ASC' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDetailJSON($as_doc='')
	{
		$ls_sql	= "	SELECT	*
					FROM	KEU_TRANSAKSI_DETAIL
					where doc_id = '".$as_doc."'";
		$result	= $this->db->query($ls_sql);			
		return json_encode( $result->row_array() );
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('KEU_TRANSAKSI', $arr_data, $arr_rule);
	}
	
	function Posting(){
		
		$ls_sql = "UPDATE gl_ledger gl
					INNER JOIN (select year, code_office, code_activity, project_code,code_coa, organization_code,
															sum(db1) db1,sum(cr1) cr1,
															sum(db2) db2,sum(cr2) cr2,
															sum(db3) db3,sum(cr3) cr3,
															sum(db4) db4,sum(cr4) cr4,
															sum(db5) db5,sum(cr5) cr5,
															sum(db6) db6,sum(cr6) cr6,
															sum(db7) db7,sum(cr7) cr7,
															sum(db8) db8,sum(cr8) cr8,
															sum(db9) db9,sum(cr9) cr9,
															sum(db10) db10,sum(cr10) cr10,
															sum(db11) db11,sum(cr11) cr11,
															sum(db12) db12,sum(cr12) cr12										
										from(
										SELECT year(bt.trans_date) year, bt.code_office, btd.code_activity, btd.project_code,btd.code_coa, btd.organization_code,
												0 begin_db,0 begin_cr,
												case  when month(bt.trans_date)=1 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db1,
												case  when month(bt.trans_date)=1 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr1, 
												case  when month(bt.trans_date)=2 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db2,
												case  when month(bt.trans_date)=2 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr2, 
												case  when month(bt.trans_date)=3 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db3,
												case  when month(bt.trans_date)=3 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr3, 
												case  when month(bt.trans_date)=4 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db4,
												case  when month(bt.trans_date)=4 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr4, 
												case  when month(bt.trans_date)=5 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db5,
												case  when month(bt.trans_date)=5 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr5, 
												case  when month(bt.trans_date)=6 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db6,
												case  when month(bt.trans_date)=6 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr6, 
												case  when month(bt.trans_date)=7 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db7,
												case  when month(bt.trans_date)=7 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr7, 
												case  when month(bt.trans_date)=8 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db8,
												case  when month(bt.trans_date)=8 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr8, 
												case  when month(bt.trans_date)=9 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db9,
												case  when month(bt.trans_date)=9 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr9, 
												case  when month(bt.trans_date)=10 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db10,
												case  when month(bt.trans_date)=10 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr10, 
												case  when month(bt.trans_date)=11 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db11,
												case  when month(bt.trans_date)=11 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr11, 
												case  when month(bt.trans_date)=12 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db12,
												case  when month(bt.trans_date)=12 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr12,
												0 end_db, 0 end_cr
										FROM gl_trans_detail btd, gl_trans bt 
										where  bt.doc_id = btd.doc_id 
										and EXISTS (select * from gl_ledger 
																	where code_coa = btd.code_coa 
																	and code_activity=btd.code_activity 
																	and project_code=btd.project_code 
																	and CAST(year  as decimal)= year(bt.trans_date)
																	and organization_code = btd.organization_code)
										GROUP BY btd.code_activity,btd.project_code,btd.code_coa, year(bt.trans_date),month(bt.trans_date), bt.code_office, btd.organization_code) z
										group by year, code_office, code_activity, project_code,code_coa, organization_code) glt
					ON gl.year = glt.year AND
					gl.code_activity = glt.code_activity AND
					gl.project_code = glt.project_code AND
					gl.code_coa = glt.code_coa AND
					gl.code_office = glt.code_office AND
					gl.organization_code = glt.organization_code
					SET gl.db1 = glt.db1,
						gl.cr1 = glt.cr1,
						gl.db2 = glt.db2,
						gl.cr2 = glt.cr2,
						gl.db3 = glt.db3,
						gl.cr3 = glt.cr3,
						gl.db4 = glt.db4,
						gl.cr4 = glt.cr4,
						gl.db5 = glt.db5,
						gl.cr5 = glt.cr5,
						gl.db6 = glt.db6,
						gl.cr6 = glt.cr6,
						gl.db7 = glt.db7,
						gl.cr7 = glt.cr7,
						gl.db8 = glt.db8,
						gl.cr8 = glt.cr8,
						gl.db9 = glt.db9,
						gl.cr9 = glt.cr9,
						gl.db10 = glt.db10,
						gl.cr10 = glt.cr10,
						gl.db11 = glt.db11,
						gl.cr11 = glt.cr11,
						gl.db12 = glt.db12,
						gl.cr12 = glt.cr12";
		$this->db->query($ls_sql);
		
		$ls_sql = "insert into gl_ledger (
										year,code_office,code_activity,project_code,code_coa,organization_code,
										begin_db,begin_cr,
										db1,cr1,
										db2,cr2,
										db3,cr3,
										db4,cr4,
										db5,cr5,
										db6,cr6,
										db7,cr7,
										db8,cr8,
										db9,cr9,
										db10,cr10,
										db11,cr11,
										db12,cr12,
										end_db,end_cr)
					select year, code_office, code_activity, project_code,code_coa, organization_code,
										sum(begin_db),sum(begin_cr),
										sum(db1),sum(cr1),
										sum(db2),sum(cr2),
										sum(db3),sum(cr3),
										sum(db4),sum(cr4),
										sum(db5),sum(cr5),
										sum(db6),sum(cr6),
										sum(db7),sum(cr7),
										sum(db8),sum(cr8),
										sum(db9),sum(cr9),
										sum(db10),sum(cr10),
										sum(db11),sum(cr11),
										sum(db12),sum(cr12),
										sum(end_db),sum(end_cr)
					from(
					SELECT year(bt.trans_date) year, bt.code_office, btd.code_activity, btd.project_code,btd.code_coa,btd.organization_code,
							0 begin_db,0 begin_cr,
							case  when month(bt.trans_date)=1 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db1,
							case  when month(bt.trans_date)=1 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr1, 
							case  when month(bt.trans_date)=2 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db2,
							case  when month(bt.trans_date)=2 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr2, 
							case  when month(bt.trans_date)=3 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db3,
							case  when month(bt.trans_date)=3 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr3, 
							case  when month(bt.trans_date)=4 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db4,
							case  when month(bt.trans_date)=4 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr4, 
							case  when month(bt.trans_date)=5 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db5,
							case  when month(bt.trans_date)=5 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr5, 
							case  when month(bt.trans_date)=6 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db6,
							case  when month(bt.trans_date)=6 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr6, 
							case  when month(bt.trans_date)=7 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db7,
							case  when month(bt.trans_date)=7 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr7, 
							case  when month(bt.trans_date)=8 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db8,
							case  when month(bt.trans_date)=8 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr8, 
							case  when month(bt.trans_date)=9 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db9,
							case  when month(bt.trans_date)=9 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr9, 
							case  when month(bt.trans_date)=10 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db10,
							case  when month(bt.trans_date)=10 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr10, 
							case  when month(bt.trans_date)=11 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db11,
							case  when month(bt.trans_date)=11 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr11, 
							case  when month(bt.trans_date)=12 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) > 0 then sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) else 0 end db12,
							case  when month(bt.trans_date)=12 and sum(ifnull(btd.debet,0))-sum(ifnull(btd.credit,0)) < 0 then sum(ifnull(btd.credit,0))-sum(ifnull(btd.debet,0)) else 0 end cr12,
							0 end_db, 0 end_cr
					FROM gl_trans_detail btd, gl_trans bt 
					where  bt.doc_id = btd.doc_id 
					and not EXISTS (select * from gl_ledger 
												where code_coa = btd.code_coa 
												and code_activity=btd.code_activity 
												and project_code=btd.project_code 
												and CAST(year  as decimal)= year(bt.trans_date)
												and organization_code = btd.organization_code)
					GROUP BY btd.code_activity,btd.project_code,btd.code_coa, year(bt.trans_date),month(bt.trans_date), bt.code_office, btd.organization_code) z
					group by year, code_office, code_activity, project_code,code_coa, organization_code";
					
		$this->db->query($ls_sql);
		
		
		$ls_sql = "update  gl_trans 
					set st_posting = 'Y',
						user_posting = '".USERID."', 
						date_posting = sysdate()		
					where st_verifikasi = 'Y' and ifnull(st_posting,'N')='N'";
		$this->db->query($ls_sql);
		
		$ls_sql = "update  gl_bank_trans 
					set trans_status = 'Y'
					where doc_id in (select reff_id from gl_trans where st_posting='Y') and ifnull(trans_status,'N')='N'";
		$this->db->query($ls_sql);
	}
}
?>